#!/bin/bash

# mysql 版本不一致导致 报错
# mysqldump 是无法使用通配符*


root@0fe243f26f5e:/usr/bin# mysqldump --opt -h192.168.10.1 -uceshi -pceshi^1_9 --skip-lock-tables ceshi_db > ceshi_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       
JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                
FROM information_schema.COLUMN_STATISTICS                

WHERE SCHEMA_NAME = 'ceshi_db' AND TABLE_NAME = 'partner_month_stat';
': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

解决方法一
可以在MySQL配置文件(例如/etc/my.cnf或〜/.my.cnf)中添加以下内容.
[mysqldump]
column-statistics=0


解决方法二
root@0fe243f26f5e:/usr/bin#
root@0fe243f26f5e:/usr/bin# 因为新版的mysqldump默认启用了一个新标志，通过 --column-statistics=0 来禁用他
root@0fe243f26f5e:/usr/bin# 看本机安装的mysql 版本
root@0fe243f26f5e:/usr/bin# mysql --version
	mysql  Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)
root@0fe243f26f5e:/usr/bin# 
root@0fe243f26f5e:/usr/bin# mysql -h192.168.10.1 -uceshi -pceshi^1_9  -e  " show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshibd_db         |
| ceshi_db           |
| swoole_db          |
| rabbitmq_db        |
| bak_swoole_db      |
| bak_swoole_db_2018 |
+--------------------+
root@0fe243f26f5e:/usr/bin# mysql -h192.168.10.1 -uceshi -pceshi^1_9  -e  " select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 5.6.47    |
+-----------+
root@0fe243f26f5e:/usr/bin# 
root@0fe243f26f5e:/usr/bin# 
root@0fe243f26f5e:/usr/bin# 看服务器的mysql 版本
root@0fe243f26f5e:/usr/bin# mysql -h192.168.10.1 -uceshi -pceshi^1_9 
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.47    |
+-----------+
1 row in set (0.02 sec)
mysql> status;
--------------
mysql  Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		586574
Current database:
Current user:		ceshi@192.168.12.2
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.6.47 MySQL Community Server (GPL)
Protocol version:	10
Connection:		192.168.10.1 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		3306
Binary data as:		Hexadecimal
Uptime:			1 day 10 hours 40 min 50 sec

Threads: 10  Questions: 3181628  Slow queries: 5  Opens: 444  Flush tables: 1  Open tables: 431  Queries per second avg: 25.483
--------------
root@0fe243f26f5e:/usr/bin# 解决方法：
root@0fe243f26f5e:/usr/bin# mysqldump --column-statistics=0 -h ip -u root -p dbname > db.sql;
root@0fe243f26f5e:/usr/bin# 
root@0fe243f26f5e:/usr/bin# 
root@0fe243f26f5e:/usr/bin# mysqldump --column-statistics=0 --opt -h192.168.10.1 -uceshi -pceshi^1_9 --skip-lock-tables ceshi_db > ceshi_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@0fe243f26f5e:/usr/bin# ls ceshi_db.sql
ceshi_db.sql
root@0fe243f26f5e:/usr/bin# ls -la ceshi_db.sql
-rw-r--r-- 1 root root 3995452 Apr 23 20:31 ceshi_db.sql
root@0fe243f26f5e:/usr/bin# 
root@0fe243f26f5e:/usr/bin# mysqldump --column-statistics=0 --opt -h192.168.10.1 -uceshi -pceshi^1_9 --skip-lock-tables swoole_db > swoole_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
root@0fe243f26f5e:/usr/bin# ls swoole_db.sql
swoole_db.sql
root@0fe243f26f5e:/usr/bin# ls -la swoole_db.sql
-rw-r--r-- 1 root root 3998956 Apr 23 21:31 swoole_db.sql


导出数据时，为避免产生乱码，需要设置编码
首先需要查看数据库编码 
show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /*保密*/ |
+--------------------------+----------------------------+

导出数据库中的某个表
mysqldump --default-character-set=utf8 -h ip -u root -p dbname tablename > /tmp/dbname_tablename.sql;
mysqldump --column-statistics=0 -h ip -u root -p dbname tablename > /tmp/dbname_tablename.sql;


导入数据库abc从abc.sql文件
mysql -h ip -u root -p password abc_db < abc.sql

ip_host=192.168.10.1
user_name=ceshi
user_password=ceshi^1_9

grep 中的 -v是排除的意思
mysql -h${ip_host} -u${user_name} -p${user_password}  -e  " show databases;" | grep -v 'Database' | grep -v 'information_schema' | grep -v 'mysql' > /tmp/databases.sql

导出部分数据
mysqldump --column-statistics=0 --opt -h${ip_host} -u${user_name} -p${user_password} -P 3306 --skip-lock-tables ${dbName} ${tableName} --where=" ${tableId} > 1 and ${tableId} < 100000 limit 3000" > ${db_table_data}/${dbName}-${tableName}.sql;


倒入数据库
方法一：
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
#mysql -uabc_f -p abc < abc.sql

方法二：
1、首先建空数据库
mysql>create database abc;
（1）选择数据库
mysql>use abc;
（2）设置数据库编码
mysql>set names utf8;
（3）导入数据（注意sql文件的路径）
mysql>source /home/abc/abc.sql;

方法三：
mysql -h${ip_host} -u${user_name} -p${user_password} --default-character-set=utf8 -e  " show databases;create database abc;use abc;set names utf8;source /home/abc/abc.sql;"


把地址db_master的库data_center中表goods_data_overview_periods 导入到另个地址的库中
mysqldump -u root -p %123456$! db_master data_center goods_data | mysql -h 172.27.16.26 xingweimiaopin_statistics




